package com.css.fxfzdpzs.modules.ypg.repository;

import com.css.fxfzdpzs.modules.ypg.entity.YpgMapGeom;
import com.css.fxfzdpzs.modules.ypg.entity.first.*;
import com.css.fxfzdpzs.modules.ypg.entity.second.*;
import com.css.fxfzdpzs.modules.ypg.vo.YpgFirstMapVO;
import com.css.fxfzdpzs.modules.ypg.vo.YpgFirstResultVO;
import com.css.fxfzdpzs.modules.ypg.vo.YpgProHouseNumCountMapVO;
import com.css.fxfzdpzs.modules.ypg.vo.YpgProSurveyPointCountMapVO;
import com.css.fxfzdpzs.util.PlatformObjectUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @description
 */
@Repository
public class YpgRepositoryImpl implements YpgRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;


    @Override
    public YpgEarthquakeSetNum getYpgEarthquakeSetNum() {
        String sql = "select * from ypg_earthquake_set_num ";
        List<YpgEarthquakeSetNum> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgEarthquakeSetNum.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<YpgProvinceEarthquakeSet> getYpgProvinceEarthquakeSets() {
        String sql = "select * from ypg_province_earthquake_set ";
        List<YpgProvinceEarthquakeSet> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProvinceEarthquakeSet.class));
        return list;
    }

    @Override
    public Ypg2015yearsEarthquakeSituation getYpg2015yearsEarthquakeSituation() {
        String sql = "select * from ypg_2015years_earthquake_situation ";
        List<Ypg2015yearsEarthquakeSituation> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Ypg2015yearsEarthquakeSituation.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }

    }

    @Override
    public List<YpgEarthquakeSetTrend> getYpgEarthquakeSetTrends() {
        String sql = "select * from ypg_earthquake_set_trend  order by years ";
        List<YpgEarthquakeSetTrend> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgEarthquakeSetTrend.class));
        return list;
    }

    @Override
    public YpgSceneCheckSituation getYpgSceneCheckSituation() {
        String sql = "select * from ypg_scene_check_situation ";
        List<YpgSceneCheckSituation> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgSceneCheckSituation.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<YpgProvinceCoverage> getYpgProvinceCoverages(String code) {
        String sql = "select id,county_coverage as coverage,province from ypg_province_coverage  where code ='"+code+"' order by coverage  desc limit 5 OFFSET 0 ";
        List<YpgProvinceCoverage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProvinceCoverage.class));
        return list;
    }

    @Override
    public YpgCountyCoverage getCountCoverage(String code) {
        String sql = "select * from ypg_coverage where code ='"+code+"' ";
        List<YpgCountyCoverage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgCountyCoverage.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<YpgProSurveyPointCount> getYpgProSurveyPointCounts(String code) {
        String sql = "select * from ypg_pro_survey_point_count  where code ='"+code+"'";
        List<YpgProSurveyPointCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProSurveyPointCount.class));
        return list;
    }

    @Override
    public YpgSurveyPointLocality getYpgSurveyPointLocality(String code) {
        String sql = "select * from ypg_survey_point_locality where code='"+code+"' ";
        List<YpgSurveyPointLocality> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgSurveyPointLocality.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<YpgSurveyPointSituation> getYpgSurveyPointSituations(String code) {
        String sql = "select * from ypg_survey_point_situation where code='"+code+"' ";
        List<YpgSurveyPointSituation> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgSurveyPointSituation.class));
        return list;
    }

    @Override
    public List<YpgHouseTypeCount> getYpgHouseTypeCounts(String code) {
        String sql = "select * from ypg_house_type_count where code='"+code+"' order by structure_type  ";
        List<YpgHouseTypeCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgHouseTypeCount.class));
        return list;
    }

    @Override
    public List<YpgProHouseNumCount> getYpgProHouseNumCounts(String code) {
        String sql = "select * from ypg_pro_house_num_count  where code='"+code+"'";
        List<YpgProHouseNumCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProHouseNumCount.class));
        return list;
    }

    @Override
    public List<YpgDamagePointTypeCount> getYpgDamagePointTypeCounts(String code) {
        String sql = "select * from ypg_damage_point_type_count where code='"+code+"' ";
        List<YpgDamagePointTypeCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgDamagePointTypeCount.class));
        return list;
    }

    @Override
    public List<YpgProDamagePointCount> getYpgProDamagePointCounts(String code) {
        String sql = "select * from ypg_pro_damage_point_count where code='"+code+"' ";
        List<YpgProDamagePointCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProDamagePointCount.class));
        return list;
    }

    @Override
    public Map<String,Object> getYpgPersonnel(int curPage,int pageSize) {
        StringBuilder sql = new StringBuilder("select * from ypg_personnel ");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(YpgPersonnel.class)).size();
        String sql1 = sql.append(limitSql).toString();
        List<YpgPersonnel> maps = jdbcTemplate.query(sql1, new BeanPropertyRowMapper<>(YpgPersonnel.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", maps);
        return map;

    }

    @Override
    public List<YpgFirstResultVO> getMap(YpgFirstMapVO ypgFirstMapVO) {
        StringBuilder sql = new StringBuilder("select ");//*
        if ("1".equals(ypgFirstMapVO.getResult())){
            sql.append(" id,longitude,latitude, die as result  from ypg_home_map ");
        }
        if ("2".equals(ypgFirstMapVO.getResult())){
            sql.append(" id,longitude,latitude, injury as result  from ypg_home_map ");
        }
        if ("3".equals(ypgFirstMapVO.getResult())){
            sql.append(" id,longitude,latitude, tent as result  from ypg_home_map ");
        }
        if ("4".equals(ypgFirstMapVO.getResult())){
            sql.append(" id,longitude,latitude, rescue as result  from ypg_home_map ");
        }
        if ("5".equals(ypgFirstMapVO.getResult())){
            sql.append(" id,longitude,latitude, personnel as result  from ypg_home_map ");
        }
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if(!PlatformObjectUtils.isEmpty(ypgFirstMapVO.getMm())){
            whereSql.append(" and    mm = '"+ypgFirstMapVO.getMm()+"'");
        }
        List<YpgFirstResultVO> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(YpgFirstResultVO.class));
        return list;
    }

    @Override
    public List<YpgFirstMap> getFirstMap() {
        String sql = "select * ,ST_AsText(geom) geom  from ypg_home_map ";
        List<YpgFirstMap> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgFirstMap.class));
        return list;
    }

    @Override
    public void saveMapGemo(List<YpgMapGeom> mapGeoms) {
        SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(mapGeoms.toArray());
        String sql = "INSERT INTO ypg_map_geom(id,mm,longitude,latitude," +
                "die_max,die_min,die_avg," +
                "injury_max,injury_min,injury_avg," +
                "tent_max,tent_min,tent_avg," +
                "rescue_max,rescue_min,rescue_avg," +
                "personnel_max,personnel_min,personnel_avg," +
                "geom) values(" +
                ":id,:mm,:longitude,:latitude," +
                ":dieMax,:dieMin,:dieAvg," +
                ":injuryMax,:injuryMin,:injuryAvg," +
                ":tentMax,:tentMin,:tentAvg," +
                ":rescueMax,:rescueMin,:rescueAvg," +
                ":personnelMax,:personnelMin,:personnelAvg,ST_GeometryFromText(:geom,4490))";
        namedParameterJdbcTemplate.batchUpdate(sql, beanSources);
    }

    @Override
    public YpgCountyCoverage getVillageCoverage(String code) {
        String sql = "select * from ypg_coverage where  code='"+code+"'";
        List<YpgCountyCoverage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgCountyCoverage.class));
        if (PlatformObjectUtils.isEmpty(list)){
            return null;
        }else {
            return list.get(0);
        }
    }

    @Override
    public List<YpgProvinceCoverage> getProvinceVillageCov(String code) {
        String sql = "select id,village_coverage as coverage,province from ypg_province_coverage  where code='"+code+"' order by coverage  limit 5 OFFSET 0 ";
        List<YpgProvinceCoverage> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgProvinceCoverage.class));
        return list;
    }

    @Override
    public List<YpgBuildMonomerCount> getBuildMonomerCount(String code) {
        String sql = "select * from ypg_build_monomer_count  where code='"+code+"' order by years  desc limit 5 OFFSET 0 ";
        List<YpgBuildMonomerCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgBuildMonomerCount.class));
        return list;
    }

    @Override
    public List<YpgDamagePointSituationCount> getDamagePointSituationCount(String code) {
        String sql = "select * from ypg_damage_point_years  where code='"+code+"' ";
        List<YpgDamagePointSituationCount> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(YpgDamagePointSituationCount.class));
        return list;
    }

    @Override
    public List<YpgProSurveyPointCountMapVO> getSurveyPointCountMap(String code,String flag) {
        StringBuilder sql = new StringBuilder("select * from ypg_pro_survey_point_map  ");
        StringBuilder whereSql = new StringBuilder(" where 1 = 1 and  flag='"+flag+"'");//*
        if (PlatformObjectUtils.isNotEmpty(code)){
           whereSql.append(" and  code='"+code+"'");//*
        }
        List<YpgProSurveyPointCountMapVO> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(YpgProSurveyPointCountMapVO.class));
        return list;
    }

}
